package com.quanquan.store.repository.statisticsReport;

import com.quanquan.store.entity.CouponCode;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @ClassName:CouponReportRepository
 * @Description:专门用来统计优惠券的接口
 * @Author:xiongjunbao
 * @Date:2019/5/30 13:50
 * @Version 1.0
 */
public interface CouponReportRepository extends JpaRepository<CouponCode, String> {

    /**
     * 根据天获取优惠券领取数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(add_time,'%Y-%m-%d') as time,count(1) as count FROM oo_coupon_code WHERE add_time is not null and type = ?1 GROUP BY time ORDER BY time DESC LIMIT 0,7",nativeQuery = true)
    List<Object[]> findAddByTypeByDay(Integer type);

    /**
     * 根据周获取优惠券领取数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(add_time,'%Y-%u') as time,count(1) as count FROM oo_coupon_code WHERE add_time is not null and type = ?1 GROUP BY time ORDER BY time DESC LIMIT 0,7",nativeQuery = true)
    List<Object[]> findAddByTypeByWeek(Integer type);

    /**
     * 根据月获取优惠券领取数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(add_time,'%Y-%m') as time,count(1) as count FROM oo_coupon_code WHERE add_time is not null and type = ?1 GROUP BY time ORDER BY time DESC LIMIT 0,7",nativeQuery = true)
    List<Object[]> findAddByTypeByMonth(Integer type);

    /**
     * 根据月获取优惠券领取数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(add_time,'%Y') as time,count(1) as count FROM oo_coupon_code WHERE add_time is not null and type = ?1 GROUP BY time ORDER BY time DESC LIMIT 0,1",nativeQuery = true)
    List<Object[]> findAddByTypeByYear(Integer type);


    /**
     * 根据天获取优惠券使用数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(use_time,'%Y-%m-%d') as time,count(1) as count FROM oo_coupon_code WHERE use_time is not null and type = ?1 GROUP BY time ORDER BY time DESC LIMIT 0,7",nativeQuery = true)
    List<Object[]> findUseByTypeByDay(Integer type);

    /**
     * 根据周获取优惠券使用数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(use_time,'%Y-%u') as time,count(1) as count FROM oo_coupon_code WHERE use_time is not null and type = ?1 GROUP BY time ORDER BY time DESC LIMIT 0,7",nativeQuery = true)
    List<Object[]> findUseByTypeByWeek(Integer type);

    /**
     * 根据月获取优惠券使用数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(use_time,'%Y-%m') as time,count(1) as count FROM oo_coupon_code WHERE use_time is not null and type = ?1 GROUP BY time ORDER BY time DESC LIMIT 0,7",nativeQuery = true)
    List<Object[]> findUseByTypeByMonth(Integer type);

    /**
     * 根据月获取优惠券使用数量
     * @return
     */
    @Query(value = "SELECT DATE_FORMAT(use_time,'%Y') as time,count(1) as count FROM oo_coupon_code WHERE use_time is not null and type = ?1 GROUP BY time ORDER BY time DESC LIMIT 0,1",nativeQuery = true)
    List<Object[]> findUseByTypeByYear(Integer type);

}
